package com.al.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.al.entity.Student;
import com.al.utils.DBHelper;

public class StudentDao {
	DBHelper db = new DBHelper();
	ResultSet rs = null;

	public List<Student> findAll() throws ClassNotFoundException, SQLException {
		//定义sql
		String sql = "select * from studentInfo order by id";
		rs = db.executeQuery(sql);
		List<Student> list = new ArrayList<Student>();
		Student student = null;
		while(rs.next()) {
			String id = rs.getString("id");
			String name = rs.getString("name");
			Integer age = rs.getInt("age");
			String gender = rs.getString("gender");
			String major = rs.getString("major");
			Long phone = rs.getLong("phone");
			student = new Student(id,name,age,gender,major,phone);
			list.add(student);
		}
		return list;
	}

	public void deleteById(String id) throws ClassNotFoundException, SQLException {
		String sql = "delete from studentInfo where id = ?";
		db.executeUpdate(sql, id);
	}

	public Student findById(String id) throws ClassNotFoundException, SQLException {
		String sql = "select * from studentInfo where id = ?";
		rs = db.executeQuery(sql, id);
		Student student = null;
		while(rs.next()) {
			String id2 = rs.getString("id");
			String name = rs.getString("name");
			Integer age = rs.getInt("age");
			String gender = rs.getString("gender");
			String major = rs.getString("major");
			Long phone = rs.getLong("phone");
			student = new Student(id2,name,age,gender,major,phone);
		}
		return student;
	}

	public int update(String id, String name, Integer age, String gender, String major, Long phone) throws ClassNotFoundException, SQLException {
		String sql = "update studentInfo set name=?,age=?,gender=?,major=?,phone=? where id=?";
		return db.executeUpdate(sql, name,age,gender,major,phone,id);
	}

	public int save(String id, String name, Integer age, String gender, String major, Long phone) throws ClassNotFoundException, SQLException {
		String sql = "insert into studentInfo values(?,?,?,?,?,?)";
		return db.executeUpdate(sql, id,name,age,gender,major,phone);
	}

}
